

import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.sql.*;
import java.text.DecimalFormat;

import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

/**
 * Servlet implementation class Real
 */
public class Real extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public Real() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		request.setCharacterEncoding("UTF-8");
		HttpSession Session=request.getSession();
		String atr=(String)request.getParameter("Begin");
		String gr=(String)request.getParameter("item");
		if (gr==null)
			gr="";
		PrintWriter out= response.getWriter();
		ResultSet rs = null;
		Statement st = null;
		Connection cn = null;
		DataSource ds = null;
		setBegin(request.getParameter("Begin"), Session);
		setEnd(request.getParameter("End"), Session);
		if (atr==null||atr.equals("")){ 
			out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.println("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Система On-line отчетов</title><style type=\"text/css\">" +
					" .treeblock{position: absolute;width: 25%;top: 0; height:100%; overflow: auto;}.mainblock{width: 70%;position: relative;top: 0;left: 30%;}</style></head><body>");
			out.println("<div class=\"mainblock\"><form method=\"get\">Начало периода:<input name=\"Begin\" value=\""+getBegin(Session)+"\"><br>Конец периода:<input name=\"End\"value=\""+getEnd(Session)+"\"><br>Склад:<select name=\"sklad\">");
			try {
				InitialContext initCtx = new InitialContext();
				ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
				cn=ds.getConnection();
				st=cn.createStatement();
				rs=st.executeQuery("select id_skl,trim(name) from sklad order by UPPER(trim(name))");
				while (rs.next()){
					out.println(String.format("<option value=\"%s\">%s</option>", rs.getString(1), rs.getString(2)));
				}
				out.println("</select><br>Группа:<input name=\"group\" value=\""+gr+"\"><br><input type=\"submit\" name=\"BOk\" value=\"     Ok!      \"></form></div>");
				rs=st.executeQuery("select trim(name) as name, id_group from GROUPID where parent_group is null order by upper(name)");
				out.println("<div class=\"treeblock\"> <br><ul>");
				String tree="";
				while (rs.next()){
					tree=tree+"<li><a href=\"real?item="+rs.getString(2)+"\"&Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&Tree=1>"+rs.getString(1)+"</li>";
				}
				if (!gr.equals("")){
					rs=st.executeQuery(String.format("select trim(name), id_group, level from GROUPID start with id_group=%s connect by prior parent_group=id_group order by level desc", gr));
					int pos=0;
					if (rs.next()){
						pos=tree.indexOf("<li><a href=\"real?item="+rs.getString(2)+"\"&Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&Tree=1>"+rs.getString(1)+"</li>")+("<li><a href=\"real?item="+rs.getString(2)+"\"&Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&Tree=1>"+rs.getString(1)).length();
						String subtree="";
						int kol=0;
						while (rs.next()){
							subtree=subtree+"<ul><li><a href=\"real?item="+rs.getString(2)+"\"&Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&Tree=1>"+rs.getString(1);
							kol++;
						}
						rs=st.executeQuery(String.format("select trim(name) as name, id_group from GROUPID where parent_group=%s order by upper(name)", gr));
						subtree=subtree+"<ul>";
						while (rs.next()){
							subtree=subtree+"<li><a href=\"real?item="+rs.getString(2)+"\"&Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&Tree=1>"+rs.getString(1)+"</li>";
						}
						subtree=subtree+"</ul>";
						for (int i=1;i<=kol;i++){
							subtree=subtree+"</li></ul>";
						}
						tree=tree.substring(0, pos)+subtree+tree.substring(pos);
					}
				}
				out.println(tree+"</ul>");

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			out.println("</body></html>");
			try {
				rs.close();
				st.close();
				cn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		else{
//			setBegin(request.getParameter("Begin"), Session);
//			setEnd(request.getParameter("End"), Session);
			setSklad(request.getParameter("sklad"), Session);
			setGroup(new Integer(request.getParameter("group")), Session);
			response.setContentType("text/html");
			response.setCharacterEncoding("UTF-8");
			String SQL=String.format("select sum(ost_begin),sum(ost_begin)*p.cost as sum_begin, sum(prih), sum(sum_prih),sum(real), sum(sum_real), sum(real_roz), sum(sum_real_roz),sum(real_roz_opt), sum(proch),sum(sum_proch), "+
					"sum(ost_begin+prih-real-real_roz-proch) as ost_end,sum(ost_begin+prih-real-real_roz-proch)*p.cost as sum_end, trim (t.name),p.cost, id from "+
					"(select sum(prih-real) as ost_begin, 0 as prih, 0 as sum_prih, 0 as real, 0 as sum_real, 0 as real_roz, 0 as sum_real_roz,0 as real_roz_opt, 0 as proch, 0 as sum_proch, id  from "+
					"(select 0 as prih, sum(l.kol) as real, l.id_tovar as id from lines l, document d,  "+
					"(select DISTINCT id_tovar from tovar WHERE id_tovar in (select id_tovar FROM kart WHERE (id_group in (select id_group FROM groupid start with id_group=%1$s CONNECT BY prior id_group=parent_group ))and id_skl=%4$s)) id "+ 
					"where d.day<'%2$s'  and not (d.numb is null) and d.id_type_doc in (select id_type_doc from type_doc where type_doc.operacia=2) and d.id_doc= l.id_doc and l.id_tovar=id.id_tovar GROUP BY l.id_tovar "+ 
					"union "+
					"select sum(l.kol) as prih, 0 as real, l.id_tovar as id from lines l, document d, "+  
					"(select DISTINCT id_tovar from tovar WHERE id_tovar in (select id_tovar FROM kart WHERE (id_group in (select id_group FROM groupid start with id_group=%1$s CONNECT BY prior id_group=parent_group ))and id_skl=%4$s)) id "+ 
					"where d.day<'%2$s'  and not (d.numb is null) and d.id_type_doc in (select id_type_doc from type_doc where type_doc.operacia=1) and d.id_doc= l.id_doc and l.id_tovar=id.id_tovar GROUP BY l.id_tovar) group by id "+
					"union "+
					"select 0 as ost_begin, sum(l.kol) as prih, sum(l.kol*(l.cost*(1-l.disc/100)*(1-d.disc/100))) as sum_prih, 0 as real, 0 as sum_real, 0 as real_roz, 0 as sum_real_roz,0 as real_roz_opt,0 as proch, "+
					"0 as sum_proch, l.id_tovar as id  from lines l, document d, "+  
					"(select DISTINCT id_tovar from tovar WHERE id_tovar in (select id_tovar FROM kart WHERE (id_group in (select id_group FROM groupid start with id_group=%1$s CONNECT BY prior id_group=parent_group ))and id_skl=%4$s)) id "+ 
					"where d.day>='%2$s' and  d.day<'%3$s'  and not (d.numb is null) and d.id_type_doc in (select id_type_doc from type_doc where operacia=1) and d.id_doc= l.id_doc and l.id_tovar=id.id_tovar GROUP BY l.id_tovar "+
					"union "+
					"select 0 as ost_begin, 0 as prih, 0 as sum_prih, sum(l.kol) as real, sum(l.kol*(l.cost*(1-l.disc/100)*(1-d.disc/100))) as sum_real, 0 as real_roz, 0 as sum_real_roz, "+
					"0 as real_roz_opt, 0 as proch, 0 as sum_proch, l.id_tovar as id from lines l, document d, client c, "+ 
					"(select DISTINCT id_tovar from tovar WHERE id_tovar in (select id_tovar FROM kart WHERE (id_group in (select id_group FROM groupid start with id_group=%1$s CONNECT BY prior id_group=parent_group ))and id_skl=%4$s)) id "+ 
					"where d.day>='%2$s' and  d.day<'%3$s'  and not (d.numb is null) and d.id_type_doc=2 and d.id_doc= l.id_doc and l.id_tovar=id.id_tovar and d.id_client=c.id_client and c.type=1 GROUP BY l.id_tovar "+
					"union "+
					"select 0 as ost_begin, 0 as prih, 0 as sum_prih, 0 as real, 0 as sum_real, sum(l.kol) as real_roz, sum(l.kol*(l.cost*(1-l.disc/100)*(1-d.disc/100))) as sum_real_roz, "+
					"sum(l.kol*p.cost) as sum_real_opt, 0 as proch, 0 as sum_proch, l.id_tovar as id  from lines l, document d, client c, (select distinct * from price) p, "+ 
					"(select DISTINCT id_tovar from tovar WHERE id_tovar in (select id_tovar FROM kart WHERE (id_group in (select id_group FROM groupid start with id_group=%1$s CONNECT BY prior id_group=parent_group ))and id_skl=%4$s)) id "+ 
					"where d.day>='%2$s' and  d.day<'%3$s'  and not (d.numb is null) and d.id_type_doc=2 and d.id_doc= l.id_doc and l.id_tovar=id.id_tovar and d.id_client=c.id_client and c.type=2 and p.id_price=(select id_price from sklad where id_skl=%4$s) and p.id_tovar=l.id_tovar "+
					"GROUP BY l.id_tovar "+
					"union "+
					"select 0 as ost_begin, 0 as prih, 0 as sum_prih, 0 as real, 0 as sum_real, 0 as real_roz, 0 as sum_real_roz,0 as real_roz_opt, sum(l.kol) as proch, "+
					"sum(l.kol*(l.cost*(1-l.disc/100)*(1-d.disc/100))) as sum_proch, l.id_tovar as id  from lines l, document d, "+  
					"(select DISTINCT id_tovar from tovar WHERE id_tovar in (select id_tovar FROM kart WHERE (id_group in (select id_group FROM groupid start with id_group=%1$s CONNECT BY prior id_group=parent_group ))and id_skl=%4$s)) id "+ 
					"where d.day>='%2$s' and  d.day<'%3$s'  and not (d.numb is null) and not(d.id_type_doc=2) and d.id_type_doc in (select id_type_doc from type_doc where operacia=2) and d.id_doc= l.id_doc and l.id_tovar=id.id_tovar GROUP BY l.id_tovar) t1, tovar t, (select distinct * from price) p where "+
					"t1.id=t.id_tovar and p.id_tovar=t1.id and p.id_price=(select id_price from sklad where id_skl=%4$s) group by trim(t.name),p.cost,t1.id order by upper(trim(t.name))",
					getGroup(Session), getBegin(Session), getEnd(Session), getSklad(Session));

			Double real=0.00;
			try {
				InitialContext initCtx = new InitialContext();
				ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
				cn=ds.getConnection();
				st=cn.createStatement();
				rs=st.executeQuery(SQL);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.println("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Система On-line отчетов</title></head><body>");
			out.println(String.format("<p align=\"center\">Отчет по движению товаров за период с %s по %s <br> (склад %s, товарная группа %s)</p>",getBegin(Session),getEnd(Session),getSkladName(Session),getGroupName(Session)) );
			out.println("<table border=\"1\" align=\"center\" cellpadding=\"5\"><thead><tr align=\"center\"><th>Наименование</th><th>Цена<br>оптовая</th><th>Остаток<br>на начало</th><th>Сумма</th><th>Приход</th><th>Сумма</th><th>Реализация<br>опт</th>"+
					"<th>Суммма</th><th>Выдано<br>на розницу</th><th>Сумма<br>в розн.ценах</th><th>Сумма<br>в опт. ценах</th><th>Прочий<br>расход</th><th>Сумма</th><th>Остаток<br>на конец</th><th>Суммма</th></tr></thead>");
			try {
				while (rs.next()){
/*					out.println(String.format("<tr><td><a href=\"%s\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td></tr>", 
							"art_move?Begin="+getBegin(Session).substring(0, 2)+"%2F"+getBegin(Session).substring(3, 5)+"%2F"+getBegin(Session).substring(6, 10)+
							"&End="+getEnd(Session).substring(0, 2)+"%2F"+getEnd(Session).substring(3, 5)+"%2F"+getEnd(Session).substring(6, 10)+
							"&name="+rs.getString(7),
							rs.getString(7),((rs.getInt(1)==0)?"-":rs.getString(1)), (rs.getInt(2)==0?"-":rs.getString(2)), (rs.getDouble(3)==0?"-":rs.getString(3)), (rs.getInt(4)==0?"-":rs.getString(4)), (rs.getDouble(5)==0?"-":rs.getString(5)), (rs.getInt(6)==0?"-":rs.getString(6))));
*/				out.println(String.format("<tr><td><a href=\"%s\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td>" +
						"<td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td></tr>", 
						"art_move?Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&name="+rs.getString(16),
						rs.getString(14),((new DecimalFormat("0.00")).format(rs.getDouble(15))).replace('.', ','),((rs.getInt(1)==0)?"-":rs.getString(1)),(rs.getDouble(2)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(2))).replace('.', ',')), 
						(rs.getInt(3)==0?"-":rs.getString(3)), (rs.getDouble(4)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(4))).replace('.', ',')),
						(rs.getInt(5)==0?"-":rs.getString(5)), (rs.getDouble(6)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(6))).replace('.', ',')),
						(rs.getInt(7)==0?"-":rs.getString(7)),(rs.getDouble(8)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(8))).replace('.', ',')),(rs.getDouble(9)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(9))).replace('.', ',')),
						(rs.getInt(10)==0?"-":rs.getString(10)), (rs.getDouble(11)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(11))).replace('.', ',')),
						(rs.getInt(12)==0?"-":rs.getString(12)), (rs.getDouble(13)==0?"-":((new DecimalFormat("0.00")).format(rs.getDouble(13))).replace('.', ','))));
				real=real+rs.getDouble(6);		
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
			out.println(String.format("</table><br><br><br> ИТОГИ: <br>Итого реализация: %s <br>", ((new DecimalFormat("0.00")).format(real)).replace('.', ',')));
			out.println("</body></html>");
			try {
				rs.close();
				st.close();
				cn.close();
				
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			

		}
	}
	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */

	private void setBegin(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("begin", Value);
		}
	}
	private void setEnd(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("end", Value);
		}
	}
	private void setGroup(int Value, HttpSession session){
		if(session!=null){
			session. setAttribute("group", Value);
		}
	}
	private void setSklad(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("sklad", Value);
		}
	}
	
	private String getBegin(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("begin");
		else
			return "";
	}
	private String getEnd(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("end");
		else
			return "";
	}
	private int getGroup(HttpSession session){
		if (session!=null)
			return (Integer)session.getAttribute("group");
		else
			return -2;
	}
	private String getSklad(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("sklad");
		else
			return "";
	}
	private String getSkladName(HttpSession session){
		ResultSet rs=null;
		String ret="";
		try {
			InitialContext initCtx = new InitialContext();
			DataSource ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
			Connection cn=ds.getConnection();
			Statement st=cn.createStatement();
			rs=st.executeQuery(String.format("select trim(name) from sklad where id_skl=%s",getSklad(session)));
			if (rs.next()){
				ret=rs.getString(1);
			}
			rs.close();
			st.close();
			cn.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	private String getGroupName(HttpSession session){
		ResultSet rs=null;
		String ret="";
		try {
			InitialContext initCtx = new InitialContext();
			DataSource ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
			Connection cn=ds.getConnection();
			Statement st=cn.createStatement();
			rs=st.executeQuery(String.format("select trim(name) from groupid where id_group=%s",getGroup(session)));
			if (rs.next()){
				ret=rs.getString(1);
			}
			rs.close();
			st.close();
			cn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
}
